package dao.impl;
import dao.TicketDao;
import entity.Ticket;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
/**
 * Created by John.C on 2018/11/19.
 */
public class TicketDaoImpl implements TicketDao{
    @Override
    //新增车票信息的方法
    public void add(Ticket ticket) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3307/ticket_salling", "root", "admin");
            String cmd = "INSERT INTO saturday (start_place,stop_place,depart_time,bus_type,price,stock) VALUES (?,?,?,?,?,?)";
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setString(1, ticket.getStartPlace());
            preparedStatement.setString(2, ticket.getStopPlace());
            preparedStatement.setString(3, ticket.getDepartTime());
            preparedStatement.setString(4, ticket.getBusType());
            preparedStatement.setInt(5, ticket.getPrice());
            preparedStatement.setInt(6, ticket.getStock());
            preparedStatement.executeUpdate();
            preparedStatement.close();
            connection.close();
        } catch (Exception exc) {
            exc.printStackTrace();
        }
    }

    @Override
    //删除车票信息的方法
    public void delete(Ticket ticket) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3307/ticket_salling", "root", "admin");
            String cmd = "DELETE FROM saturday WHERE start_place=? AND stop_place=? AND depart_time=? AND bus_type=?";
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setString(1, ticket.getStartPlace());
            preparedStatement.setString(2, ticket.getStopPlace());
            preparedStatement.setString(3, ticket.getDepartTime());
            preparedStatement.setString(4, ticket.getBusType());
            preparedStatement.executeUpdate();
            preparedStatement.close();
            connection.close();
        } catch (Exception exc) {
            exc.printStackTrace();
        }
    }

    @Override
    //修改剩余车票的数量
    public void update(Ticket ticket) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3307/ticket_salling", "root", "admin");
            String cmd = "UPDATE saturday SET stock=? WHERE start_place=? AND stop_place=? AND depart_time=? AND bus_type=?";
            PreparedStatement preparedStatement = connection.prepareStatement(cmd);
            preparedStatement.setInt(1, ticket.getStock());
            preparedStatement.setString(2, ticket.getStartPlace());
            preparedStatement.setString(3, ticket.getStopPlace());
            preparedStatement.setString(4, ticket.getDepartTime());
            preparedStatement.setString(5, ticket.getBusType());
            preparedStatement.executeUpdate();
            preparedStatement.close();
            connection.close();
        } catch (Exception exc) {
            exc.printStackTrace();
        }
    }

    @Override
    //根据发车地和到站地查询车票信息，并打印所有相关项
    public Ticket query(String startPlace, String stopPlace) {
        Ticket ticket=new Ticket();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3307/ticket_salling", "root", "admin");
            String sql = "SELECT * FROM saturday WHERE start_place=? AND stop_place=?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, startPlace);
            preparedStatement.setString(2, stopPlace);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                String startPlace1 = resultSet.getString("start_place");
                String stopPlace1 = resultSet.getString("stop_place");
                String departTime = resultSet.getString("depart_time");
                String busType = resultSet.getString("bus_type");
                int price = resultSet.getInt("price");
                int stock = resultSet.getInt("stock");
                System.out.println(startPlace1 + " 到 " + stopPlace1 + "    发车时间：" + departTime + "    车型：" + busType + "    票价：" + price + "    剩余票数：" + stock);
                ticket.setStartPlace(startPlace1);
                ticket.setStopPlace(stopPlace1);
                ticket.setDepartTime(departTime);
                ticket.setBusType(busType);
                ticket.setPrice(price);
                ticket.setStock(stock);
            }
            resultSet.close();
            preparedStatement.close();
            connection.close();
        } catch (Exception exc) {
            exc.printStackTrace();
        }
        return ticket;
    }
}